﻿using System;
using System.Collections.Generic;

using System.Configuration;
using System.Data;
using System.Data.SqlClient;


using Galaxy.Vinder.BO;

namespace Galaxy.Vinder.DAL
{
    public class TipDB
    {

        private static String connectionString = AppConfiguration.ConnectionString;

        public static Tip getTip(int id)
        {
            Tip tip = null;
            String cmd = "select t.id t_id, t.titulo t_titulo, t.descripcion t_descripcion, s.id s_id, s.nombre s_nombre, s.descripcion s_descripcion, s.imagen s_imagen " +
            "from tip t inner join servicio s on t.servicioId=s.id where t.id= " + id;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(cmd, connection);
                command.CommandType = CommandType.Text;
                command.CommandTimeout = 120;

                connection.Open();
                using (SqlDataReader myReader = command.ExecuteReader())
                {
                    if (myReader.Read())
                    {
                        tip = FillDataRecord(myReader);
                    }
                    myReader.Close();
                }
                connection.Close();
            }
            return tip;
        }

        public static List<Tip> getTodosLosTips()
        {
            List<Tip> tips = null;
            String cmd = "select * from tip";
            using (SqlConnection connection = new SqlConnection(
                "Data Source=TITAN\\SQLEXPRESS;Initial Catalog=vinder;Integrated Security=True"))
            {
                SqlCommand command = new SqlCommand(cmd, connection);
                command.CommandType = CommandType.Text;

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        tips = new List<Tip>();
                        while (reader.Read())
                        {
                            tips.Add(FillDataRecord(reader));
                        }
                    }
                    reader.Close();
                }
                connection.Close();
            }
            return tips;
        }

        public static long salvar(Tip tip)
        {
            long insertID = -1;
            string sqlIns;
            bool insert = false;
            if (tip.Id == -1)
            {
                sqlIns = "INSERT INTO tip (titulo, descripcion, servicioId) VALUES (@titulo, @descripcion, @servicioId)";
                insert = true;
            }
            else
            {
                sqlIns = "update tip set titulo = @titulo, descripcion = @descripcion, servicioId= @servicioId where id=" + tip.Id;
            }

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmdIns = new SqlCommand(sqlIns, connection);
                connection.Open();
                cmdIns.Parameters.AddWithValue("@titulo", tip.Titulo);
                cmdIns.Parameters.AddWithValue("@descripcion", tip.Descripcion);
                cmdIns.Parameters.AddWithValue("@servicioId", tip.Servicio.Id);
                try
                {
                    cmdIns.ExecuteNonQuery();
                }
                catch (SqlException error)
                {

                }

                cmdIns.Parameters.Clear();
                cmdIns.CommandText = "SELECT @@IDENTITY";
                if (insert)
                {
                    insertID = Convert.ToInt64(cmdIns.ExecuteScalar());
                }
                else
                {
                    insertID = tip.Id;
                }
                cmdIns.Dispose();
                cmdIns = null;
                connection.Close();
            }
            return insertID;
        }

        public static bool borrar(int id)
        {
            int result = 0;
            using (SqlConnection connection = new SqlConnection("Data Source=TITAN\\SQLEXPRESS;Initial Catalog=vinder;Integrated Security=True"))
            {
                String cmd = "delete from tip where id= " + id;
                SqlCommand command = new SqlCommand(cmd, connection);
                command.CommandType = CommandType.Text;
                connection.Open();
                result = command.ExecuteNonQuery();
                connection.Close();
            }
            return result > 0;
        }

        private static Tip FillDataRecord(IDataRecord data)
        {
            Tip tip = new Tip();
            tip.Servicio = new Servicio();

            tip.Id = data.GetInt64(data.GetOrdinal("t_id"));
            tip.Titulo = data.GetString(data.GetOrdinal("t_titulo"));
            if (!data.IsDBNull(data.GetOrdinal("t_descripcion")))
            {
                tip.Descripcion = data.GetString(data.GetOrdinal("t_descripcion"));
            }
            tip.Servicio.Id = data.GetInt64(data.GetOrdinal("s_id"));
            tip.Servicio.Nombre = data.GetString(data.GetOrdinal("s_nombre"));
            if (!data.IsDBNull(data.GetOrdinal("s_descripcion")))
            {
                tip.Servicio.Descripcion = data.GetString(data.GetOrdinal("s_descripcion"));
            }
            if (!data.IsDBNull(data.GetOrdinal("s_imagen")))
            {
                tip.Servicio.ImagePath = data.GetString(data.GetOrdinal("s_imagen"));
            }
            return tip;
        }
    }
}